#!/usr/bin/env python
# Create: 2019/5/29
__author__ = '749B'
"""查询所有端口的流量统计"""

import requests
import time

from sds.swis_login import swis

# 限制条件
SQL_WHERE = "WHERE nc.Department IN ('IP核心网', '园区_IP网', '金桥DC_IP网', '蓝光DC_IP网', '蓝光3号楼DC_IP网') " \
            "AND i.InterfaceAlias != '' AND i.StatusDescription = 'Up' "

# 限定时间，这个是UTC时间
# 对应北京时间要在下面的时间的基础上再加8小时
SQL_WHEN = "DateTime > '2019-04-30 16:00:00' AND DateTime <= '2019-05-29 08:00:00'"

INTERFACE_COUNT = 0  # 总共要查询的端口数
TOP_NUM = 10  # 每次请求的数量


def get_count():
    """统计符合要求的总数"""
    sql_str = "SELECT COUNT(i.Uri) AS uri_count " \
              "FROM Orion.NPM.Interfaces i " \
              "JOIN Orion.NodesCustomProperties nc ON i.NodeID = nc.NodeID {sql_where} "
    sql_str = sql_str.format(sql_where=SQL_WHERE)
    results = swis.query(sql_str)
    if not results:
        return 0
    res = results.pop('results')
    if len(res) == 0:
        return 0
    return res[0].get('uri_count')


def get_interface(iid):
    """获取端口的信息"""
    sql_str = "SELECT TOP @top_num " \
              "i.FullName, i.InterfaceAlias, i.InterfaceCaption, i.InterfaceID, i.InterfaceName, " \
              "ic.Comments AS InterfaceComments, " \
              "n.Caption AS NodeName, n.IPAddress, " \
              "nc.City, nc.Department, nc.Comments AS NodeComments " \
              "FROM Orion.NPM.Interfaces i " \
              "JOIN Orion.NPM.InterfacesCustomProperties ic ON i.InterfaceID = ic.InterfaceID " \
              "JOIN Orion.Nodes n ON i.NodeID = n.NodeID " \
              "JOIN Orion.NodesCustomProperties nc ON i.NodeID = nc.NodeID " \
              "{sql_where} AND i.InterfaceID > @iid " \
              "ORDER BY i.InterfaceID"
    sql_str = sql_str.format(sql_where=SQL_WHERE)
    results = swis.query(sql_str, top_num=TOP_NUM, iid=iid)
    if not results:
        print(iid, "Interface 查询未返回内容", results)
        return
    res = results.pop('results')
    if results:
        print(iid, "Interface 查询返回其他内容", results)
        return
    if len(res) < 1:
        return
    return res


def traffic_by_id(iids):
    sql_str = "SELECT InterfaceID, Min(DateTime) AS S_Time , Max(DateTime) AS E_Time, " \
              "Avg(InAveragebps) AS InAvg, " \
              "Max(InMaxbps) AS InMax, " \
              "Min(InMinbps) AS InMin, " \
              "Avg(OutAveragebps) AS OutAvg, " \
              "Max(OutMaxbps) AS OutMax, " \
              "Min(OutMinbps) AS OutMin " \
              "FROM Orion.NPM.InterfaceTraffic " \
              "WHERE InterfaceID IN ({iids}) AND {sql_when} " \
              "GROUP BY InterfaceID"
    sql_str = sql_str.format(sql_when=SQL_WHEN, iids=','.join(map(lambda x: str(x), iids)))
    results = swis.query(sql_str)
    if not results:
        print(iids, "Traffic 查询未返回内容", results)
        return
    res = results.pop('results')
    if results:
        print(iids, "Traffic 查询返回其他内容", results)
        return
    if len(res) < 1:
        return
    return res


def list_to_dict(data):
    """把id单独取出来，返回一个列表。并把其余数据以id为key返回字典"""
    ids = list()  # 存放所有的id
    info = dict()  # 以id为key，存放原来其他的数据
    for item in data:
        iid = item.pop('InterfaceID')
        ids.append(iid)
        info.setdefault(iid, item)
    return ids, info


def dic_to_line(data, iid, keys):
    data.setdefault("InterfaceID", iid)
    l = list()
    for k in keys:
        s = str(data.get(k)).replace(',', '_')  # 返回None要处理成字符串"None"，如果有逗号，也要替换掉
        l.append(s)
    return ','.join(l)


CSV_KEYS = ('FullName', 'InterfaceAlias', 'InterfaceCaption', 'InterfaceID', 'InterfaceName',
            'InterfaceComments', 'NodeName', 'IPAddress', 'City', 'Department', 'NodeComments',
            'S_Time', 'E_Time',
            'InAvg', 'InMax', 'InMin', 'OutAvg', 'OutMax', 'OutMin')


def main():
    global INTERFACE_COUNT
    INTERFACE_COUNT = get_count()
    if INTERFACE_COUNT < 10:
        exit("端口数量太少，可能程序有错")
    current_iid = 0
    count = 0
    res_f = open('traffic_results2.csv', 'w', encoding='utf-8')
    res_f.writelines((','.join(CSV_KEYS), '\n'))
    start = time.time()
    while True:
        info = get_interface(current_iid)
        if not info:
            break
        iids, infos = list_to_dict(info)
        current_iid = max(iids)
        traffic = traffic_by_id(iids)
        if not traffic:
            print(current_iid, "该端口没有流量")
            continue
        _, traffics = list_to_dict(traffic)

        for iid in infos:
            # 先按每个端口id做合并
            infos[iid].update(traffics[iid])
            # 将合并后的一个端口的字典写入文件
            line = dic_to_line(infos[iid], iid, CSV_KEYS)
            res_f.writelines((line, '\n'))
            count += 1
        print('\r进度(%d/%d), id: %d, 用时: %.2f秒' % (
            count, INTERFACE_COUNT, current_iid, time.time()-start), end='', flush=True)
        # if count > 80:  # 测试10条
        #     break
    res_f.close()


requests.packages.urllib3.disable_warnings()


if __name__ == '__main__':
    main()
    print("\n运行结束")



